package com.code.apollo.utils;

import com.code.apollo.pojo.Column;
import com.code.apollo.pojo.DB;
import com.code.apollo.pojo.Table;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * <Description>
 * 元数据库工具类
 * @author Jun Lee
 * @version 1.0
 * @createDate 2020/03/16 11:32
 * @see com.code.apollo.utils
 */
public class DBUtil {

    /**
     * 获取数据库连接
     * @param db
     * @return
     * @throws Exception
     */
    public static Connection getConnection(DB db) throws Exception {
        Properties props = new Properties();
        props.put("remarksReporting","true");
        props.put("user",db.getUserName());
        props.put("password",db.getPassWord());
        Class.forName(DB.driver);
        return DriverManager.getConnection(db.getUrl(),props);
    }


    /**
     * 获取数据库列表
     * @param db
     * @return
     * @throws Exception
     */
    public static List<String> getSchemas(DB db) throws Exception {
        Connection connection = getConnection(db);
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet rs = metaData.getCatalogs();
        List<String> list = new ArrayList<>();
        while (rs.next()) {
            list.add(rs.getString(1));
        }
        rs.close();
        connection.close();
        return list;
    }


    /**
     * 获取数据库元数据
     * @param db
     * @return
     * @throws Exception
     */
    public static List<Table> getDbInfo(DB db) throws Exception {
        Connection connection = getConnection(db);
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet tables = metaData.getTables(null, null, null, new String[]{"TABLE"});
        List<Table> tableList = new ArrayList<>();
        while (tables.next()) {
            String tableName = tables.getString("TABLE_NAME");
            Table table = Table.builder()
                    .name(tableName)
                    .lowerName(StringUtils.removePrefix(tableName))
                    .className(StringUtils.getClassName(tableName))
                    .comment(tables.getString("REMARKS"))
                    .build();
            ResultSet keys = metaData.getPrimaryKeys(null, null, tableName);
            String key = "";
            while (keys.next()) {
                String columnName = keys.getString("COLUMN_NAME");
                key += columnName+",";
            }
            table.setKey(key);
            ResultSet columns = metaData.getColumns(null, null, tableName, null);
            List<Column> columnList = new ArrayList<>();
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                Column column = Column.builder()
                        .columnName(columnName)
                        .fieldName(StringUtils.toJavaVariableName(columnName))
                        .columnDbType(columns.getString("TYPE_NAME"))
                        .columnType(JavaTypes.getType(columns.getInt("DATA_TYPE")))
                        .columnComment(columns.getString("REMARKS"))
                        .build();
                String pri = null;
                if(StringUtils.contains(columnName ,key.split(","))) {
                    pri = "PRI";
                }
                column.setColumnKey(pri);
                columnList.add(column);
            }
            columns.close();
            table.setColumns(columnList);
            tableList.add(table);
        }
        tables.close();
        connection.close();
        System.out.println(tableList);
        return tableList;
    }
}
